if object_id('BIMonitor.SSISProblemSummaryReport') is not null
  drop procedure BIMonitor.SSISProblemSummaryReport
go


create procedure BIMonitor.SSISProblemSummaryReport
  @Environment varchar(75)

as


select
  ProblemType,
  SSRSProblemTypeSort,
    case 
    when ProblemType = 'Failure' then 'Failure'
    when ProblemType = 'Error' then 'Error'
    else 'Full Detail'
  end                      as SSISLogReportEventDetail,
  [LT1Count],
  case 
    when ProblemType = 'Failure' and [LT1Count] > 0 then 'Red'
    when ProblemType = 'Error' and [LT1Count] > 0 then 'Orange'
    when ProblemType = 'Warning' and [LT1Count] > 0 then 'Yellow'
    else 'White'
  end                      as SSRSBackgroundColorLT1,
  case
    when ProblemType = 'Failure' and [LT1Count] > 0 then 'Blue'
    when ProblemType = 'Error' and [LT1Count] > 0 then 'Blue'
    else 'Blue'
  end                      as SSRSFontColorLT1,
  case
    when ProblemType = 'Failure' and [LT1Count] > 0 then 'Bold'
    when ProblemType = 'Error' and [LT1Count] > 0 then 'Bold'
    when ProblemType = 'Warning' and [LT1Count] > 0 then 'Bold'
    else 'Normal'
  end                      as SSRSFontWeightLT1,
  [1_2Count],
  case 
    when ProblemType = 'Failure' and [1_2Count] > 0 then 'Red'
    when ProblemType = 'Error' and [1_2Count] > 0 then 'Orange'
    when ProblemType = 'Warning' and [1_2Count] > 0 then 'Yellow'
    else 'White'
  end                      as SSRSBackgroundColor1_2,
  case
    when ProblemType = 'Failure' and [1_2Count] > 0 then 'Blue'
    when ProblemType = 'Error' and [1_2Count] > 0 then 'Blue'
    else 'Blue'
  end                      as SSRSFontColor1_2,
  case
    when ProblemType = 'Failure' and [1_2Count] > 0 then 'Bold'
    when ProblemType = 'Error' and [1_2Count] > 0 then 'Bold'
    when ProblemType = 'Warning' and [1_2Count] > 0 then 'Bold'
    else 'Normal'
  end                      as SSRSFontWeight1_2,
  [3_6Count],
  case 
    when ProblemType = 'Failure' and [3_6Count] > 0 then 'Red'
    when ProblemType = 'Error' and [3_6Count] > 0 then 'Orange'
    when ProblemType = 'Warning' and [3_6Count] > 0 then 'Yellow'
    else 'White'
  end                      as SSRSBackgroundColor3_6,
  case
    when ProblemType = 'Failure' and [3_6Count] > 0 then 'Blue'
    when ProblemType = 'Error' and [3_6Count] > 0 then 'Blue'
    else 'Blue'
  end                      as SSRSFontColor3_6,
  case
    when ProblemType = 'Failure' and [3_6Count] > 0 then 'Bold'
    when ProblemType = 'Error' and [3_6Count] > 0 then 'Bold'
    when ProblemType = 'Warning' and [3_6Count] > 0 then 'Bold'
    else 'Normal'
  end                      as SSRSFontWeight3_6,
  [7_12Count],
  case 
    when ProblemType = 'Failure' and [7_12Count] > 0 then 'Red'
    when ProblemType = 'Error' and [7_12Count] > 0 then 'Orange'
    when ProblemType = 'Warning' and [7_12Count] > 0 then 'Yellow'
    else 'White'
  end                      as SSRSBackgroundColor7_12,
  case
    when ProblemType = 'Failure' and [7_12Count] > 0 then 'Blue'
    when ProblemType = 'Error' and [7_12Count] > 0 then 'Blue'
    else 'Blue'
  end                      as SSRSFontColor7_12,
  case
    when ProblemType = 'Failure' and [7_12Count] > 0 then 'Bold'
    when ProblemType = 'Error' and [7_12Count] > 0 then 'Bold'
    when ProblemType = 'Warning' and [7_12Count] > 0 then 'Bold'
    else 'Normal'
  end                      as SSRSFontWeight7_12,
  [13_24Count],
  case 
    when ProblemType = 'Failure' and [13_24Count] > 0 then 'Red'
    when ProblemType = 'Error' and [13_24Count] > 0 then 'Orange'
    when ProblemType = 'Warning' and [13_24Count] > 0 then 'Yellow'
    else 'White'
  end                      as SSRSBackgroundColor13_24,
  case
    when ProblemType = 'Failure' and [13_24Count] > 0 then 'Blue'
    when ProblemType = 'Error' and [13_24Count] > 0 then 'Blue'
    else 'Blue'
  end                      as SSRSFontColor13_24,
  case
    when ProblemType = 'Failure' and [13_24Count] > 0 then 'Bold'
    when ProblemType = 'Error' and [13_24Count] > 0 then 'Bold'
    when ProblemType = 'Warning' and [13_24Count] > 0 then 'Bold'
    else 'Normal'
  end                      as SSRSFontWeight13_24,
  convert(nvarchar(30),CheckDate,100) as CheckDate
from
  BIMonitor.SSISProblemSummary
where
  Environment = @Environment